package fr.isen.smssender.database;

/**
 * Created by IntelliJ IDEA.
 * User: Alexis Coppens M1
 * Date: 19/01/12
 * Time: 10:46
 */

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class SmsSenderSQLite extends SQLiteOpenHelper {


    public static final String TABLE_CONTACT = "table_contacts";
    public static final String COL_ID = "_id";
    public static final String COL_NOM = "Nom";
    public static final String COL_PRENOM = "Prenom";
    public static final String COL_BIRTHDAY = "Birthday";
    public static final String COL_NUMBER = "Number";
    public static final int NUM_COL_ID = 0;
    public static final int  NUM_COL_NOM = 1;
    public static final int NUM_COL_PRENOM = 2;
    public static final int NUM_COL_BIRTHDAY = 3;
    public static final int NUM_COL_NUMBER = 4;

    public static final String TABLE_SMS = "table_sms";
    public static final String COL_ID_SMS = "_id";
    public static final String COL_MESSAGE = "Message";

    public static final String TABLE_ToSend = "table_sms_to_send";
    public static final String COL_ID_SEND = "_id";
    public static final String COL_ID_CONTACT = "_idContact";
    public static final String COL_ID_SEND_SMS = "_idSMS";
    public static final String COL_DATE = "_date";

    private static final String CREATE_BDD =
            "CREATE TABLE " + TABLE_CONTACT + " ("
                    + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + COL_NOM + " TEXT NOT NULL, "
                    + COL_PRENOM + " TEXT NOT NULL, "
                    + COL_BIRTHDAY + " DATE,"
                    + COL_NUMBER + " TEXT NOT NULL);"
            ;

    private static final String CREATE_SMS_TABLE =
            "CREATE TABLE " + TABLE_SMS + " ("
                    + COL_ID_SMS + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + COL_MESSAGE + " TEXT NOT NULL);";

    private static final String CREATE_SEND_TABLE =
            "CREATE TABLE "+TABLE_ToSend+"("
                    + COL_ID_SEND +" int AUTO_INCREMENT NOT NULL,"
                    + COL_ID_CONTACT +" INT NOT NULL,"
                    + COL_ID_SEND_SMS+" INT, "
                    +COL_DATE+" DATE, PRIMARY KEY ("+COL_ID_SEND_SMS+") );" +
                    "  ALTER TABLE "+TABLE_ToSend +"ADD CONSTRAINT FK_"+TABLE_ToSend+"_"+COL_ID_CONTACT+"FOREIGN KEY ("+COL_ID_CONTACT+") REFERENCES Contact ("+COL_ID+");" +
                    " ALTER TABLE "+TABLE_ToSend +" ADD CONSTRAINT FK_"+TABLE_ToSend+"_"+COL_ID_SMS+" FOREIGN KEY ("+COL_ID_SEND_SMS+") REFERENCES SMS ("+COL_ID_SMS+");";





    public SmsSenderSQLite(Context context, String name, CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //on créé la table à partir de la requête écrite dans la variable CREATE_BDD
        db.execSQL(CREATE_BDD);
        db.execSQL(CREATE_SMS_TABLE);
        db.execSQL(CREATE_SEND_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //On peut fait ce qu'on veut ici moi j'ai décidé de supprimer la table et de la recréer
        //comme ça lorsque je change la version les id repartent de 0
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACT + ";");
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_SMS + ";");
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ToSend + ";");
        onCreate(db);
    }

}